package com.us.usws.dao;

import java.util.ArrayList;
import java.util.List;

import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.exception.DBException;
import net.iwsh.core.utils.IDGenerator;

import org.apache.commons.lang3.StringUtils;

import com.us.usws.common.SiteConstants;
import com.us.usws.model.domain.video.Video;
import com.us.usws.model.flat.channel.DOChannelVideoLink;
import com.us.usws.model.flat.video.DOVideo;
import com.us.usws.model.flat.video.DOVideoFile;
import com.us.usws.support.uitls.DataPageUtil;
import com.us.usws.support.uitls.Order;

public class VideoDAO extends BasePageSearchDAO<Video> {

	private static final String SQL_GET_VIDEOS_BY_CHANNEL_UID = "select v.* from hv_channel c inner join hv_channel_video_link l on l.channel_uid=c.uid inner join hv_video v on v.uid=l.video_uid where c.is_valid='Y' and v.auth_status_dsid=? and c.uid=?";
	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from hv_video";
	private static final String SQL_GET_ALL_VIDEOS = "select * from hv_video order by seq_num";
	private static final String SQL_GET_VIDEO = "select * from hv_video where uid=?";
	
	private static final String SQL_GET_VIDEO_WITH_VIDEOFILE = "select count(*) from hv_video where file_uid=?";
	

	private static final String SQL_SELECT_VIDEO_CAT_LINK = "select * from hv_video_cat_link where video_uid=? and cat_uid=? ";
	//private static final String SQL_GET_VIDEO_FILE = "select * from hv_video_file where uid=?";
	//private static final String SQL_GET_VIDEO_CATS = "select c.* from hv_video_cat_link l inner join hvb_video_cat c on l.cat_uid=c.uid where l.video_uid=?";

	private static final String SQL_GET_VIDEOS_BY_THEME_UID = "select v.* from hv_video v inner join box_theme_video_link l on l.video_uid=v.uid inner join box_theme t on t.uid=l.theme_uid where t.uid=?";

	//FIXME: this is a fake biz logic
	private static final String SQL_GET_RECOMMEND_VIDEOS = "select * from hv_video order by rand() limit ?";
	
	private static final String SQL_GET_CHANNEL_VIDEO_CAT_LINK = "select * from hv_channel_video_link where video_uid = ? and channel_uid=?";

	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_VIDEOFILE = "insert into hv_video_file(uid,orig_file_name,snapshot,file_size,mime_type,play_len,file_ext,create_by_uid,create_date,last_upd_by_uid,last_upd_date) values (?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_VIDEOFILE = "update hv_video_file set orig_file_name=?, snapshot=?, file_size=?, mime_type=?, play_len=?, file_ext=?, last_upd_by_uid=?, last_upd_date=? where uid=?";

	private static final String SQL_UPDATE_VIDEOFILE_SNAPSHOT = "update hv_video_file set snapshot=?, play_len=? where uid=?";

	private static final String SQL_UPDATE_VIDEOFILE_PLAYLEN = "update hv_video_file set play_len=? where uid=?";

	private static final String SQL_UPDATE_VIDEO_FILE = "update hv_video_file set orig_file_name=?, file_size=?, mime_type=?, play_len=?, file_ext=?, last_upd_by_uid=?, last_upd_date=? where uid=?";

	private static final String SQL_INSERT_VIDEO_CAT_LINK = "insert into hv_video_cat_link(video_uid,cat_uid) values (?,?)";

	private static final String SQL_INSERT_VIDEO = "insert into hv_video(uid,file_uid,name,type_sid,tags,auth_status_dsid,seq_num,create_by_uid,create_date,last_upd_by_uid,last_upd_date,description) values (?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_VIDEO = "update hv_video set file_uid=?, name=?, type_sid=?, tags=?, auth_status_dsid=?, seq_num=?, last_upd_by_uid=?, last_upd_date=?, description=? where uid=?";

	private static final String SQL_DEL_VIDEO = "delete from hv_video where uid=?";
	
	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * get channel videos
	 * @param channelUid
	 * @return
	 */
	public List<Video> getVideosByChannel(String channelUid) {
		return dbe.list(SQL_GET_VIDEOS_BY_CHANNEL_UID, Video.class, SiteConstants.VIDEO_AUTH_STATUS_PUBLISHED, channelUid);
	}

	/**
	 * get channel video count
	 * @param channelUid
	 * @return
	 */
	public int getVideoCountByChannel(String channelUid) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_VIDEOS_BY_CHANNEL_UID);
		return dbe.getCount(sql, SiteConstants.VIDEO_AUTH_STATUS_PUBLISHED, channelUid);
	}

	/**
	 * get channel videos with pagination
	 * @param channelUid
	 * @param start
	 * @param end
	 * @return
	 */
	public List<Video> getVideosByChannel(String channelUid, int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_VIDEOS_BY_CHANNEL_UID,
				new Object[] { SiteConstants.VIDEO_AUTH_STATUS_PUBLISHED, channelUid }, start, end);
		return dbe.list(swp.getSql(), Video.class, swp.getParams());
	}

	/**
	 * get video
	 * @param videoUid
	 * @return
	 */
	public Video getVideo(String videoUid) {
		return dbe.load(SQL_GET_VIDEO, Video.class, videoUid);
	}

	/**
	 * Get all list.
	 */
	public List<Video> getAllVideos() {
		return dbe.list(SQL_GET_ALL_VIDEOS, Video.class);
	}

	/**
	 * filter page search
	 * @param dataPageUtil
	 */
	public void loadVideosByPage(DataPageUtil<Video> dataPageUtil) {
		String countSql = "select count(*) from hv_video where auth_status_dsid<>7";
		String sql = " select * from hv_video where auth_status_dsid<>7";
		SQLWithParams swp = initSQLWithParams(dataPageUtil, null, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), Video.class, swp.getParams()));
	}

	public void loadVideosAbleAddByPage(DataPageUtil<Video> dataPageUtil, String channelUid) {
		String countSql = "select count(*) from hv_video v where auth_status_dsid=4 and "
				+ "not exists(select 1 from hv_channel_video_link cvl where cvl.video_uid = v.uid and cvl.channel_uid=?)";
		String sql = " select * from hv_video v where auth_status_dsid=4 and "
				+ "not exists(select 1 from hv_channel_video_link cvl where cvl.video_uid = v.uid and cvl.channel_uid=?)";
		List<Object> filedValues = new ArrayList<Object>();
		filedValues.add(channelUid);
		SQLWithParams swp = initSQLWithParams(dataPageUtil, filedValues, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), Video.class, swp.getParams()));
	}

	public void loadChannelVideosByPage(DataPageUtil<Video> dataPageUtil, String channelUid) {
		String countSql = "select count(*) from hv_video v inner join hv_channel_video_link cvl on v.uid= cvl.video_uid where "
				+ " cvl.channel_uid=? ";
		String sql = " select * from hv_video v inner join hv_channel_video_link cvl on v.uid= cvl.video_uid where "
				+ " cvl.channel_uid=? ";
		List<Object> filedValues = new ArrayList<Object>();
		filedValues.add(channelUid);
		dataPageUtil.setOrder(Order.desc("cvl.seq_num"));
		SQLWithParams swp = initSQLWithParams(dataPageUtil, filedValues, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), Video.class, swp.getParams()));
	}

	/**
	 * Get all list by pagination.
	 */
	public List<Video> getVideos(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_VIDEOS, null, start, end);
		return dbe.list(swp.getSql(), Video.class, swp.getParams());
	}

	public List<DOVideo> getRecommendVideos(String videoUid, int cnt) {
		return dbe.list(SQL_GET_RECOMMEND_VIDEOS, DOVideo.class, cnt);
	}

	/**
	 * get theme videos
	 * @param themeUid
	 * @return
	 */
	public List<Video> getThemeVideos(String themeUid) {
		return dbe.list(SQL_GET_VIDEOS_BY_THEME_UID, Video.class, themeUid);
	}

	/**
	 * Insert one row.
	 */
	public String insertVideo(DOVideo video) {
		String uid = IDGenerator.getInstance().genUID();
		video.setUid(uid);
		dbe.execute(SQL_INSERT_VIDEO, video.getUid(), video.getFileUid(), video.getName(), video.getTypeSid(), video.getTags(),
				video.getAuthStatusDsid(), video.getSeqNum(), video.getCreateByUid(), video.getCreateDate(), video.getLastUpdByUid(),
				video.getLastUpdDate(), video.getDescription());
		return uid;
	}

	public boolean insertVideo(DOVideo video, DOVideoFile videoFile, String[] catIds) {
		dbe.startTransaction();
		try {
			dbe.executeBatch(SQL_INSERT_VIDEOFILE, videoFile.getUid(), videoFile.getOrigFileName(), videoFile.getSnapshot(),
					videoFile.getFileSize(), videoFile.getMimeType(), videoFile.getPlayLen(), videoFile.getFileExt(),
					videoFile.getCreateByUid(), videoFile.getCreateDate(), videoFile.getLastUpdByUid(), videoFile.getLastUpdDate());

			dbe.executeBatch(SQL_INSERT_VIDEO, video.getUid(), video.getFileUid(), video.getName(), video.getTypeSid(), video.getTags(),
					video.getAuthStatusDsid(), video.getSeqNum(), video.getCreateByUid(), video.getCreateDate(), video.getLastUpdByUid(),
					video.getLastUpdDate(), video.getDescription());

			if (catIds != null && catIds.length > 0) {
				for (String catId : catIds) {
					dbe.executeBatch(SQL_INSERT_VIDEO_CAT_LINK, video.getUid(), catId);
				}
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		}
		dbe.commitTransaction();
		return true;

	}

	/**
	 * Update by PK.
	 */
	public void updateVideo(DOVideo video) {
		dbe.execute(SQL_UPDATE_VIDEO, video.getFileUid(), video.getName(), video.getTypeSid(), video.getTags(), video.getAuthStatusDsid(),
				video.getSeqNum(), video.getLastUpdByUid(), video.getLastUpdDate(), video.getDescription(), video.getUid());
	}

	/**
	 * Update by PK.
	 */
	public void updateVideo(DOVideo video, DOVideoFile videoFile, String[] catIds) {
		dbe.startTransaction();
		try {
			if (StringUtils.isNotBlank(videoFile.getSnapshot()) && StringUtils.isBlank(videoFile.getOrigFileName())) {
				/*
				 * only update snapshot
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEOFILE_SNAPSHOT, videoFile.getSnapshot(), videoFile.getPlayLen(), videoFile.getUid());
			} else if (StringUtils.isNotBlank(videoFile.getOrigFileName()) && StringUtils.isBlank(videoFile.getSnapshot())) {
				/*
				 * only update video info 
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEO_FILE, videoFile.getOrigFileName(), videoFile.getFileSize(), videoFile.getMimeType(),
						videoFile.getPlayLen(), videoFile.getFileExt(), videoFile.getLastUpdByUid(), videoFile.getLastUpdDate(),
						videoFile.getUid());
			} else if (StringUtils.isNotBlank(videoFile.getSnapshot()) && StringUtils.isNotBlank(videoFile.getOrigFileName())) {
				/*
				 * update all info
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEOFILE, videoFile.getOrigFileName(), videoFile.getSnapshot(), videoFile.getFileSize(),
						videoFile.getMimeType(), videoFile.getPlayLen(), videoFile.getFileExt(), videoFile.getLastUpdByUid(),
						videoFile.getLastUpdDate(), videoFile.getUid());
			} else {
				dbe.executeBatch(SQL_UPDATE_VIDEOFILE_PLAYLEN, videoFile.getPlayLen(), videoFile.getUid());
			}
			dbe.executeBatch(SQL_UPDATE_VIDEO, video.getFileUid(), video.getName(), video.getTypeSid(), video.getTags(),
					video.getAuthStatusDsid(), video.getSeqNum(), video.getLastUpdByUid(), video.getLastUpdDate(), video.getDescription(),
					video.getUid());

			if (catIds != null && catIds.length > 0) {
				for (String catId : catIds) {
					Object o = dbe.load(SQL_SELECT_VIDEO_CAT_LINK, video.getUid(), catId);
					if (o == null) {
						dbe.executeBatch(SQL_INSERT_VIDEO_CAT_LINK, video.getUid(), catId);
					}
				}
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}
	
	public DOChannelVideoLink getDOChannelVideoLink(String videoUid, String channelUid) {
		return dbe.load(SQL_GET_CHANNEL_VIDEO_CAT_LINK, DOChannelVideoLink.class, videoUid, channelUid);
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteVideo(String uid) {
		dbe.execute(SQL_DEL_VIDEO, uid);
	}
	
	public int getVideoCountByFileUid(String fileUid) {
		return dbe.getCount(SQL_GET_VIDEO_WITH_VIDEOFILE, fileUid);
	}

}
